﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

using Newtonsoft.Json;
using System.Configuration;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using System.Dynamic;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using Microsoft.JScript;


namespace TradeWeb.Controllers
{
    public class InvoiceDocController : Controller
    {
        //
        // GET: /SalePact/

        public ActionResult Index()
        {
            return View();
        }

        //查询销售合同列表－[分页+模糊查询]
        public string GetInvoiceDocListPages(string strUserCode, string strPageIndex, string strPageSizes, string strSearchWhere)
        {
            string jsonResult = string.Empty;
            //获取用户权限
            //获取销售合同数据
            //是否需要排序
            SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
            try
            {
                //打开数据库连接
                sqlConn.Open();

                //权限－下辖成员权限编号
                List<string> lstManagerUser = new List<string>();
                //权限组、下辖成员子集、单个下辖成员
                string strUserPowerGroup = string.Empty;
                string strUserCodeList = string.Empty;
                string strManagerUserS = string.Empty;

                #region 获取用户权限UserPowerConditon

                string strSqlUser = "select * from T_User where UserCode='" + strUserCode + "'";
                SqlCommand sqlComUser;
                SqlDataAdapter sqlAdaUser;
                DataSet dsUser = new DataSet();
                sqlComUser = new SqlCommand(strSqlUser, sqlConn);
                sqlAdaUser = new SqlDataAdapter(sqlComUser);
                sqlAdaUser.Fill(dsUser, "T_User");
                if (dsUser.Tables[0].DefaultView.Count == 0)
                {
                    return jsonResult = "{\"success\":true  ,\"data\":{}   ,\"message\":\"你的帐户已经冻结或不存在!\"}";
                }


                string isHighPower = dsUser.Tables[0].DefaultView[0]["ISHighPower"].ToString();
                //如果是超级管理员：最高权限；
                if (isHighPower == "是")
                {
                    string strSqlAll = "select * from T_User";
                    SqlCommand sqlComAll;
                    SqlDataAdapter sqlAdaAll;
                    DataSet dsAll = new DataSet();
                    sqlComAll = new SqlCommand(strSqlAll, sqlConn);
                    sqlAdaAll = new SqlDataAdapter(sqlComAll);
                    sqlAdaAll.Fill(dsAll, "T_All");
                    foreach (DataRowView drv in dsAll.Tables[0].DefaultView)
                    {
                        lstManagerUser.Add(drv["UserCode"].ToString().ToLower());
                    }
                }
                else
                {
                    if (dsUser.Tables.Count > 0 && dsUser.Tables[0].Rows.Count > 0)
                    {
                        strUserPowerGroup = dsUser.Tables[0].DefaultView[0]["UserPowerGroup"].ToString();
                        strUserCodeList = dsUser.Tables[0].DefaultView[0]["UserCodeList"].ToString();
                    }
                    if (string.IsNullOrEmpty(strUserPowerGroup.Trim()))
                    {
                        //没有从组继承权限
                        strManagerUserS = strUserCodeList;
                    }
                    else
                    {
                        //如果继承了组权限
                        string strSQLGroup = "SELECT * FROM T_UserPowerGroup WHERE UserPowerGroup='" + strUserPowerGroup + "'";
                        SqlCommand sqlComGroup;
                        SqlDataAdapter sqlAdaGroup;
                        DataSet dsGroup = new DataSet();
                        sqlComGroup = new SqlCommand(strSQLGroup, sqlConn);
                        sqlAdaGroup = new SqlDataAdapter(sqlComGroup);
                        sqlAdaGroup.Fill(dsGroup, "T_PowerGroup");
                        strManagerUserS = dsGroup.Tables[0].DefaultView[0]["UserCodeList"].ToString();
                    }
                    string[] strS = strManagerUserS.Split('|');
                    foreach (string str in strS)
                    {
                        if (str.Trim() == string.Empty)
                            continue;
                        lstManagerUser.Add(str);
                    }
                    if (lstManagerUser.IndexOf(strUserCode) < 0)
                        lstManagerUser.Add(strUserCode);
                }
                #endregion

                //分解合成下辖成员集合：生成UserPowerConditon
                string strUserPower = string.Empty;
                #region 分解合成UserPowerConditon

                string strWhere1 = "";
                string strWhere2 = "";

                //这个地方是有值的"001|002|001|002|001|002|001|002|001|002|001|002";所以重置一下
                strManagerUserS = "";
                for (int i = 0; i < lstManagerUser.Count; i++)
                {
                    if (strManagerUserS == "")
                        strManagerUserS = "'" + lstManagerUser[i] + "'";
                    else
                        strManagerUserS = strManagerUserS + ",'" + lstManagerUser[i] + "'";
                }
                strManagerUserS = "(" + strManagerUserS + ")";
                string strENColumnName = "Creater";
                if (strWhere1 == "")
                    strWhere1 = strENColumnName + "='" + strUserCode + "'";
                else
                    strWhere1 = strWhere1 + " OR " + strENColumnName + "='" + strUserCode + "'";

                if (strWhere2 == "")
                    strWhere2 = strENColumnName + " IN " + strManagerUserS;
                else
                    strWhere2 = strWhere2 + " AND " + strENColumnName + " IN " + strManagerUserS;

                strENColumnName = "UserCode";
                if (strWhere1 == "")
                    strWhere1 = strENColumnName + "='" + strUserCode + "'";
                else
                    strWhere1 = strWhere1 + " OR " + strENColumnName + "='" + strUserCode + "'";
                if (strWhere2 == "")
                    strWhere2 = strENColumnName + " IN " + strManagerUserS;
                else
                    strWhere2 = strWhere2 + " OR " + strENColumnName + " IN " + strManagerUserS;

                if (String.IsNullOrEmpty(strWhere1) == false && String.IsNullOrEmpty(strWhere1) == false)
                    strUserPower = " ((" + strWhere1 + ") OR (" + strWhere2 + ")) ";

                #endregion


                //加上用户权限条件
                string strWhere = string.Empty;
                if (!String.IsNullOrEmpty(strUserPower))
                {
                    strWhere = strWhere + " AND " + strUserPower + " ";
                }
                //加上模糊查询的条件
                if (!string.IsNullOrEmpty(strSearchWhere.Trim()))
                {
                    strSearchWhere = strSearchWhere.Trim();
                    strSearchWhere = Microsoft.JScript.GlobalObject.decodeURIComponent(strSearchWhere);
                    string strSearch = " ( InvoiceNum LIKE '%" + strSearchWhere + "%' OR ExpPactNums LIKE '%" + strSearchWhere + "%'  OR CustPactNums LIKE '%" + strSearchWhere + "%' OR ClientName LIKE '%" + strSearchWhere + "%' OR CNUserName like '%" + strSearchWhere + "%' )";
                    strWhere += strWhere + " AND " + strSearch;
                }

                //查询记录的数目
                string strSqlCount = "SELECT COUNT(*) Row_Count FROM V_InvoiceDOC Where isDel=0 " + strWhere;
                SqlCommand sqlComCount;
                SqlDataAdapter sqlAdaCount;
                DataSet dsResult = new DataSet();
                sqlComCount = new SqlCommand(strSqlCount, sqlConn);
                sqlAdaCount = new SqlDataAdapter(sqlComCount);
                sqlAdaCount.Fill(dsResult, "T_Count");


                //查询列表的数据
                string strSqlResult = "SELECT * FROM (SELECT ROW_NUMBER() over(order by InvoiceDate DESC,InvoiceNum) as colnum,ISDel,InvoiceNum,ClientCode,UserCode,CreaterName,CNUserName,Creater,AimCountry,ShippingMode,InvoiceDate,CustMoneyStatus,InvoiceDOCComplete,AccountComplete,ClientName,ExpPactNums,CustPactNums,ExportPort,AimPort,TakeNo,InvoiceStatus,SendAuditState,AuditState,OpenShipDate,PriceClause,Payment,Curr,CustMoney,NoCustMoney,TotalSaleMoney FROM V_InvoiceDOC where  ISDel=0 " + strWhere + " )  tab_1";
                SqlCommand sqlComResult;
                SqlDataAdapter sqlAdaResult;

                //加上分页的条件
                if (string.IsNullOrEmpty(strPageSizes))
                {
                    strPageSizes = "10";
                }
                string strSearchPage = string.Empty;
                if (!string.IsNullOrEmpty(strPageIndex)) 
                {
                    strSearchPage = " WHERE colnum>(" + strPageIndex + "-1)*" + strPageSizes + " AND colnum<=" + strPageIndex + "*" + strPageSizes + "";
                }
                if (!string.IsNullOrEmpty(strSearchPage)) 
                {
                    strSqlResult = strSqlResult + strSearchPage;
                }

                sqlComResult = new SqlCommand(strSqlResult, sqlConn);
                sqlAdaResult = new SqlDataAdapter(sqlComResult);
                sqlAdaResult.Fill(dsResult, "T_InvoiceDoc");

                sqlConn.Close();
                jsonResult = "{\"success\":true  ,\"data\":" + JsonConvert.SerializeObject(dsResult) + "  ,\"message\":\"正常\"}";
                return jsonResult;
            }
            catch (Exception ex)
            {
                jsonResult = "{\"success\":false  ,\"data\":{}   ,\"message\":\"" + ex.Message + "\"}";
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
            return jsonResult;
        }

        
    }
}
